import pymysql,utils as md5

from flask import Flask,request,render_template,url_for,redirect

app = Flask(__name__)

conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="cpvs_cls_sys",
    charset="utf8"
)

cursor = conn.cursor()

frequency = 4

@app.route('/')
def home():
    return redirect(url_for('login'))

@app.route('/hello')
def hello():
    return "hello world!"

def get_students():
    sql = """select name,age,gender from cpvs_10_19"""
    cursor.execute(sql)
    res = cursor.fetchall()

    student = []
    for i in res:
        name = i[0]
        age = i[1]
        gender = i[2]
        students_table = {"name":name,"age":age,"gender":gender}
        student.append(students_table)
    return student

@app.route('/home',methods=['GET'])
def show_students():
    students_list = get_students()
    return render_template("students_table.html" , students = students_list)

@app.route('/login',methods=["GET","POST"])
def login():
    global frequency

    if request.method == 'GET':
        return render_template('into_user.html')
    username = request.form.get('username')
    password = request.form.get("password")
    if not username:
        return """
            <script type="text/javascript">
                alert("用户名不能为空！");
            </script>
        """
    if 3 < len(password) < 8:
        sql_select = f"""select id,name from user where username='{username}';"""
        with conn.cursor() as cursor:
            cursor.execute(sql_select)
            res = cursor.fetchall()
        if not res:
            return render_template("into_user.html")

        sql_select_2 = f"""select id,name from user where username='{username}' and password='{md5.create_token(password)}';"""
        print(sql_select_2)
        with conn.cursor() as cursor:
            cursor.execute(sql_select_2)
            res = cursor.fetchall()
            if not res:
                if frequency > 1:
                    frequency = frequency - 1
                    print(frequency)
                    return redirect(url_for("login"))
                else:
                    # return render_template("update_pwd.html")
                    return redirect(url_for("update_pwd"))
            return redirect(url_for('home'))
    return '''
                <script type="text/javascript">
                            alert("密码在3~8之间");
                        </script>
                '''

if __name__ == '__main__':
    app.run(debug=True)